

import pandas as pd

from sqlalchemy import create_engine

def model(dbt, session):

    # my_sql_model_df = dbt.ref("my_first_dbt_model") # 可以从model中提取数据
    # final_df =  my_sql_model_df # stuff you can't write in SQL!

    # final_df = pd.read_excel(r"C:\Users\Administrator\Desktop\tiktok_seller_sku.xlsx")
    # print(f"我是:{dbt.config.get('materialized')}")
    # print(f"我是:{dbt.config.get('tags')}")
    # print(f"我是:{dbt.config.get('remote_warehouse')}")

    # 连接数据库
    con_url = dbt.config.get('remote_warehouse')
    today = pd.to_datetime('today').strftime('%Y-%m-%d')
    # 时间前移30天 
    begin = (pd.to_datetime('today') - pd.Timedelta(days=30)).strftime('%Y-%m-%d')
    sql = f"""
    select FROM_UNIXTIME(statement_time, '%%Y-%%m-%%d') as statement_date,
    if(account_id in ('1570', '1254', '1208', '1082', '1081', '1080', '905', '918'),'本土','跨境') as store_type,* 
    from jp_saas_erp_order.dcm_order_tts_jp_original_report 
    where statement_time BETWEEN UNIX_TIMESTAMP('{begin}') and UNIX_TIMESTAMP('{today}')
    """
    # 数据返回给dbt
    final_df = pd.read_sql(sql, con=create_engine(con_url))

    print(final_df.info())
    
    return final_df



